package com.ihr360.payroll.dao;

import com.ihr360.payroll.model.dto.staff.StaffSalaryProfile;
import com.ihr360.rest.reposity.SimpleRestReposity;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;

import java.util.Date;
import java.util.List;
import java.util.Set;

@RepositoryRestResource
public interface StaffSalaryProfileRepository extends SimpleRestReposity<StaffSalaryProfile, String> {
    List<StaffSalaryProfile> findByCompanyIdAndStaffIdIn(String companyId, Set<String> staffIds);

   /* @Query(value = "select staff.id as staffId ," +
            "staff.staffName as staffName," +
            "staff.departmentName as departmentName, " +
            " profile.id as staffSalaryProfileId, " +
            "staff.mobileNo as mobileNo " +
            " from sc_synced_basic_staff_info staff " +
            " left Join StaffSalaryProfile profile " +
            " on staff.id = profile.staffId " +
            " and profile.effectiveAt <= :currentDate " +
            " and (profile.inValidAt is null or profile.inValidAt >= :currentDate) " +
            " and profile.effectiveAt is not null " +
            " where staff.companyId = :companyId and staff.staffStatus = :staffStatus and staff.id in :authStaffIds order by staff.enrollInDate desc \n#pageable\n ",
            countQuery = "select count(*) from sc_synced_basic_staff_info staff left Join StaffSalaryProfile profile on staff.id = profile.staffId " +
                    " and profile.effectiveAt <= :currentDate " +
                    " and (profile.inValidAt is null or profile.inValidAt >= :currentDate) " +
                    " and profile.effectiveAt is not null " +
                    " where staff.companyId = :companyId and staffStatus = :staffStatus  and staff.id in :authStaffIds ", nativeQuery = true)
    Page<Object[]> getCurrentEffectiveData(@Param("companyId") String companyId,
                                           @Param("staffStatus") String staffStatus,
                                           @Param("currentDate") Date currentDate,
                                           @Param("authStaffIds") List<String> authStaffIds,
                                           Pageable pageable);*/

    @Query(value = "select staff.id as staffId ," +
            "staff.staffName as staffName," +
            "staff.departmentName as departmentName, " +
            " profile.id as staffSalaryProfileId, " +
            "staff.mobileNo as mobileNo, " +
            "staff.staffStatus as staffStatus " +
            " from sc_synced_basic_staff_info staff " +
            " left Join StaffSalaryProfile profile " +
            " on staff.id = profile.staffId " +
            " and profile.effectiveAt <= :currentDate " +
            " and (profile.inValidAt is null or profile.inValidAt >= :currentDate) " +
            " and profile.effectiveAt is not null " +
            " where  staff.id in :authStaffIds order by staff.enrollInDate desc ",
            nativeQuery = true)
    List<Object[]> getCurrentEffectiveData(@Param("currentDate") Date currentDate,
                                           @Param("authStaffIds") List<String> authStaffIds);


    @Query(value = "select s from StaffSalaryProfile  s " +
            " where companyId = :companyId " +
            " and staff.id  = :staffId " +
            " and effectiveAt is not null " +
            " and effectiveAt > :currentDate ")
    List<StaffSalaryProfile> findAfterCurrentDatas(@Param("companyId") String companyId, @Param("staffId") String staffId, @Param("currentDate") Date currentDate);


    @Modifying
    @Query(value = " delete from StaffSalaryProfile where companyId = :companyId and staff.id in (:modifyStaffIds) ")
    void deleteByCompanyIdAndStaffIds(@Param("companyId") String companyId, @Param("modifyStaffIds") Set<String> modifyStaffIds);

    List<StaffSalaryProfile> findByCompanyIdAndStaffId(String companyId, String staffId);

    @Query(value = " select staff.id from StaffSalaryProfile  where companyId = :companyId ")
    List<String> findExistStaffIdsByCompanyId(@Param("companyId") String companyId);

    @Query(value = "select s from StaffSalaryProfile  s " +
            " where companyId = :companyId " +
            " and staff.id  = :staffId " +
            " and effectiveAt is not null " +
            " and effectiveAt <= :currentDate " +
            " and (inValidAt is null or inValidAt >= :currentDate)")
    StaffSalaryProfile getStaffCurrentSalaryProfile(@Param("companyId") String companyId, @Param("staffId") String staffId, @Param("currentDate") Date currentDate);
}
